﻿CREATE PROCEDURE [dbo].[Report_CategorySameStoreComparisonHistoryBenchmark]
	   @RangeReviewId int
	, @CategoryId smallint	
	, @HistoryGroupId int
	, @IsPriorYear bit
AS

Select 
	groups.Name
	, groups.Name Title
	, @CategoryId CategoryId
	, details.Category
	, CASE WHEN @IsPriorYear = 1 THEN AVG(details.SS_VolumePY) ELSE AVG(details.SS_VolumeCY) END AS Units
	, CASE WHEN @IsPriorYear = 1 THEN AVG(details.SS_SalesPY * history.Rate / members.TargetRate) ELSE AVG(details.SS_SalesCY * history.Rate / members.TargetRate) END AS Sales
	, CASE WHEN @IsPriorYear = 1 THEN AVG(details.SS_GIMPY * history.Rate / members.TargetRate) ELSE AVG(details.SS_GIMCY * history.Rate / members.TargetRate) END AS GM
	, CASE WHEN @IsPriorYear = 1 THEN AVG(details.SS_VolumePY / total.SS_VolumePY) ELSE AVG(details.SS_VolumeCY / total.SS_VolumeCY) END AS UnitsShare
	, CASE WHEN @IsPriorYear = 1 THEN AVG(details.SS_SalesPY / total.SS_SalesPY) ELSE AVG(details.SS_SalesCY / total.SS_SalesCY) END AS SalesShare
	, CASE WHEN @IsPriorYear = 1 THEN AVG(details.SS_GIMPY / total.SS_GIMPY) ELSE AVG(details.SS_GIMCY / total.SS_GIMCY) END AS GMShare 
	, CASE WHEN @IsPriorYear = 1 THEN AVG(details.SS_PIDiffPY / NULLIF(details.SS_SalesPY,0)) ELSE AVG(details.SS_PIDiffCY / NULLIF(details.SS_SalesCY,0)) END AS PIDiff
	, CASE WHEN @IsPriorYear = 1 THEN AVG(details.SS_ChargeOffPY / NULLIF(details.SS_SalesPY,0)) ELSE AVG(details.SS_ChargeOffCY / NULLIF(details.SS_SalesCY,0)) END AS ChargeOffs
	, CASE WHEN @IsPriorYear = 1 THEN AVG(details.SS_InventoryPY * history.Rate / members.TargetRate) ELSE AVG(details.SS_InventoryCY * history.Rate / members.TargetRate) END AS InventoryCost
	, LinearSpace = AVG(history.SpacesConversion / members.TargetSpacesConversion * (details.Gondola + details.TransactionArea + details.GrabAndGo + details.WIC + details.OpenCooler + details.Freezer + details.Forecourt + details.OtherExhibition))
	, AVG(details.SKUsWithSales) SKUs
	, AVG(details.Sales80) Sales80
	, @IsPriorYear IsPriorYear
From RangeReviewBenchmarks rrb WITH (NOLOCK)
Inner Join HistoryGroupMembers members WITH (NOLOCK) on rrb.HistoryGroupId = members.HistoryGroupId
Inner Join HistoryGroups groups WITH (NOLOCK) on rrb.HistoryGroupId = groups.Id
Inner Join History history WITH (NOLOCK) on members.HistoryId = history.Id
Inner Join HistoryDetails details WITH (NOLOCK) on history.Id = details.HistoryId
Inner Join HistoryDetails total WITH (NOLOCK) on history.Id = total.HistoryId AND total.CategoryId IS NULL and total.Category = 'TOTAL STORE'
Where 
	rrb.RangeReviewSourceId = @RangeReviewId 
	AND rrb.HistoryGroupId = @HistoryGroupId
	AND ((@CategoryId = 0 AND details.CategoryId IS NULL and details.Category = 'TOTAL STORE') OR (@CategoryId != 0 AND details.CategoryId = @CategoryId))
Group By
	groups.Name
	, details.Category
	